package com.pe.pgn.clubpgn.dao.impl;

import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.pe.pgn.clubpgn.common.CLPConstantes;
import com.pe.pgn.clubpgn.dao.VehiculoDao;
import com.pe.pgn.clubpgn.dao.hibernate.GenericDaoHibernate;
import com.pe.pgn.clubpgn.domain.ClpbTipoVehiculo;
import com.pe.pgn.clubpgn.domain.ClpbVehiculo;
import com.pe.pgn.clubpgn.webapp.util.ValidationUtil;

@Repository
public class VehiculoDaoImpl extends GenericDaoHibernate<ClpbVehiculo, Long> implements VehiculoDao{

	private JdbcTemplate jdbcTemplate;
	
	@Autowired
	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}
	
	public VehiculoDaoImpl() {
		super(ClpbVehiculo.class);
	}

	@SuppressWarnings("unchecked")
	public List<ClpbVehiculo> obtenerVehiculos() {

		StringBuffer query = new StringBuffer();
		query.append(" select ");
		query.append(" 	v.id, v.de_placa as dePlaca, v.de_modelo as deModelo, tv.de_tipo_vehiculo as deTipoVehiculo ");
		query.append(" from clpb_vehiculo v ");
		query.append(" join clpb_tipo_vehiculo tv on tv.id = v.co_tipo_vehiculo ");
		query.append(" where tv.st_tipo_vehiculo = '").append(CLPConstantes.ST_VERDADERO).append("'");
		query.append(" and v.st_vehiculo = '").append(CLPConstantes.ST_VERDADERO).append("'");
		
		return findListOfBeans(query.toString(), null, ClpbVehiculo.class);
	}

	@SuppressWarnings("unchecked")
	public List<ClpbVehiculo> buscarVehiculos(ClpbVehiculo bnvehiculo) {

		StringBuffer query = new StringBuffer();
		query.append(" select ");
		query.append(" 	v.id, v.de_placa as dePlaca, v.de_modelo as deModelo, tv.de_tipo_vehiculo as deTipoVehiculo ");
		query.append(" from clpb_vehiculo v ");
		query.append(" join clpb_tipo_vehiculo tv on tv.id = v.co_tipo_vehiculo ");
		query.append(" where tv.st_tipo_vehiculo = '").append(CLPConstantes.ST_VERDADERO).append("'");
		query.append(" and v.st_vehiculo = '").append(CLPConstantes.ST_VERDADERO).append("'");
		
		if(StringUtils.isNotBlank(bnvehiculo.getDePlaca()))
		query.append(" and upper(v.de_placa) like upper('%").append(bnvehiculo.getDePlaca()).append("%')");
		
		if(StringUtils.isNotBlank(bnvehiculo.getDeModelo()))
		query.append(" and upper(v.de_modelo) like upper('%").append(bnvehiculo.getDeModelo()).append("%')");
		
		ClpbTipoVehiculo tipovehiculo = bnvehiculo.getClpbTipoVehiculo();
		if((tipovehiculo != null && tipovehiculo.getId() != null) && !(tipovehiculo.getId().toString()).equals("-1"))
		query.append(" and v.co_tipo_vehiculo = ").append(tipovehiculo.getId());
		
		return findListOfBeans(query.toString(), null, ClpbVehiculo.class);
	}

	@SuppressWarnings("unchecked")
	public ClpbVehiculo obtenerVehiculo(Long id) {

		ClpbVehiculo bean = new ClpbVehiculo();
		StringBuffer query = new StringBuffer();
		
		query.append(" select ");
		query.append(" 	v.id, v.de_placa as dePlaca, v.de_modelo as deModelo, ");
		query.append("  v.co_tipo_vehiculo as coTipoVehiculo, tv.de_tipo_vehiculo as deTipoVehiculo, ");
		query.append(" 	v.st_excluido_control_fraude as stExcluidoControlFraude, v.st_vehiculo as stVehiculo ");
		
		query.append(" from clpb_vehiculo v ");
		query.append(" join clpb_tipo_vehiculo tv on tv.id = v.co_tipo_vehiculo ");
		query.append(" where tv.st_tipo_vehiculo = '").append(CLPConstantes.ST_VERDADERO).append("'");
		query.append(" and v.st_vehiculo = '").append(CLPConstantes.ST_VERDADERO).append("'");
		query.append(" and v.id = ").append(id);
		
		List list = jdbcTemplate.queryForList(query.toString());
		if(ValidationUtil.validateList(list)){
			
			Map map = (HashMap)list.get(0);
			bean.setId(new Long(map.get("id").toString()));
			bean.setDePlaca(map.get("dePlaca").toString());
			bean.setDeModelo(map.get("deModelo").toString());
			bean.setCoTipoVehiculo(map.get("coTipoVehiculo").toString());
			bean.getClpbTipoVehiculo().setId(new Long(map.get("coTipoVehiculo").toString()));
			
			if((map.get("stExcluidoControlFraude").toString()).equals(CLPConstantes.ST_VERDADERO))
			bean.setStExcluidoControlFraude(true);
			
			if((map.get("stVehiculo").toString()).equals(CLPConstantes.ST_VERDADERO))
			bean.setStVehiculo(true);
		}
		
		return bean;
	}

	@SuppressWarnings("unchecked")
	public Map eliminarVehiculo(Long id) {

		Map map = new HashMap();
		StringBuffer query = new StringBuffer();
		
		query.append(" update clpb_vehiculo ");
		query.append(" set st_vehiculo = '").append(CLPConstantes.ST_FALSO).append("'");
		query.append(" where id = ").append(id);
		
		int size = jdbcTemplate.update(query.toString());
		if(size > 0){
			map.put("msg", "success");
		}else{
			map.put("msg", "error");
		}
		
		return map;
	}

	@SuppressWarnings("unchecked")
	public Map guardarVehiculo(ClpbVehiculo bnvehiculo) {

		Map map = new HashMap();
		
		try{
		
			bnvehiculo.getClpbTipoVehiculo().setId(new Long(bnvehiculo.getCoTipoVehiculo()));
			bnvehiculo.setStVehiculo(true);
			
			if(bnvehiculo.getId() == null){
				
				bnvehiculo.setCoUsuarioCreador(getUsuarioLogueado().getUsername());
				bnvehiculo.setDaFechaCreacion(Calendar.getInstance());
			}
			else{
				bnvehiculo.setCoUsuarioModificador(getUsuarioLogueado().getUsername());
				bnvehiculo.setDaFechaModificacion(Calendar.getInstance());
			}	
			
			getHibernateTemplate().saveOrUpdate(bnvehiculo);
			getHibernateTemplate().flush();
			
			map.put("msg", "success");
			map.put("id", bnvehiculo.getId());		
		
		}catch(Exception e){
			map.put("msg", "error");				
		}
		
		return map;
	}
}
